using System;
using Entities;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace DAL
{
    public class tbSanPhamDAL
    {
        private SqlConnection cn = null;
        public tbSanPhamDAL()
        {
            cn = new SqlConnection(ConfigurationManager.ConnectionStrings["strConnection"].ConnectionString);
            cn.Open();
        }
        public int Insert(tbSanPhamEntities sanpham)
        {
            SqlCommand cm = new SqlCommand("sp_tbSanPham_Insert", cn);
            cm.CommandType = System.Data.CommandType.StoredProcedure;
            //cm.Parameters.AddWithValue("@MaSP", sanpham.MaSP);
            cm.Parameters.AddWithValue("@TenSP", sanpham.TenSP);
            cm.Parameters.AddWithValue("@MoTa", sanpham.MoTa);            
            cm.Parameters.AddWithValue("@MaLoai", sanpham.MaLoai);
            cm.Parameters.AddWithValue("@MaNV", sanpham.MaNV);
            cm.Parameters.AddWithValue("@MaDN", sanpham.MaDN);
            cm.Parameters.AddWithValue("@SLCon", sanpham.SLCon);
            cm.Parameters.AddWithValue("@SLBan", sanpham.SLBan);
            cm.Parameters.AddWithValue("@GiaGoc", sanpham.GiaGoc);
            cm.Parameters.AddWithValue("@GiaBan", sanpham.GiaBan);
            cm.Parameters.AddWithValue("@NgayCapNhat", sanpham.NgayCapNhat);         
            cm.Parameters.AddWithValue("@MaTT", sanpham.MaTT);
           return int.Parse(cm.ExecuteScalar().ToString());
        }
        public void Update(tbSanPhamEntities sanpham)
        {
            SqlCommand cm = new SqlCommand("sp_tbSanPham_Update", cn);
            cm.CommandType = System.Data.CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@MaSP", sanpham.MaSP);
            cm.Parameters.AddWithValue("@TenSP", sanpham.TenSP);
            cm.Parameters.AddWithValue("@MoTa", sanpham.MoTa);           
            cm.Parameters.AddWithValue("@MaLoai", sanpham.MaLoai);
            cm.Parameters.AddWithValue("@MaNV", sanpham.MaNV);
            cm.Parameters.AddWithValue("@MaDN", sanpham.MaDN);
            cm.Parameters.AddWithValue("@SLCon", sanpham.SLCon);
            cm.Parameters.AddWithValue("@SLBan", sanpham.SLBan);
            cm.Parameters.AddWithValue("@GiaGoc", sanpham.GiaGoc);
            cm.Parameters.AddWithValue("@GiaBan", sanpham.GiaBan);
            cm.Parameters.AddWithValue("@NgayCapNhat", sanpham.NgayCapNhat);
            cm.Parameters.AddWithValue("@MaTT", sanpham.MaTT);
            cm.ExecuteNonQuery();
        }
        public void Delete(int ID)
        {
            SqlCommand cm = new SqlCommand("sp_tbSanPham_Delete", cn);
            cm.CommandType = System.Data.CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@MaSP", ID);
            cm.ExecuteNonQuery();
        }
        public tbSanPhamEntities.tbSanPhanEntitiesCollection  Get(string where, int pageIndex,
            int pageSize, string orderBy, string orderDirection, out int totalRecords)
            {
                SqlCommand cm = new SqlCommand("sp_tbSanPham_GET", cn);
                cm.CommandType = System.Data.CommandType.StoredProcedure;
                cm.Parameters.AddWithValue("@Where", where);
                cm.Parameters.AddWithValue("@PageIndex", pageIndex);
                cm.Parameters.AddWithValue("@PageSize", pageSize);
                cm.Parameters.AddWithValue("@OrderBy", orderBy);
                cm.Parameters.AddWithValue("@OrderDirection",
                orderDirection);
                cm.Parameters.Add(new SqlParameter("@TotalRecords",System.Data.DbType.Int32)).Direction = System.Data.ParameterDirection.Output;
                SqlDataReader dr = cm.ExecuteReader();
                tbSanPhamEntities.tbSanPhanEntitiesCollection SanPhamCollection = new tbSanPhamEntities.tbSanPhanEntitiesCollection();
                while (dr.Read())
                    {
                        tbSanPhamEntities sp = new tbSanPhamEntities();
                        sp.MaSP = int.Parse(dr.GetValue(0).ToString());
                        sp.TenSP = dr.GetValue(1).ToString();
                        sp.MoTa = dr.GetValue(2).ToString();                      
                        sp.MaLoai = int.Parse(dr.GetValue(3).ToString());
                        sp.MaNV = int.Parse(dr.GetValue(4).ToString());
                        sp.MaDN = int.Parse(dr.GetValue(5).ToString());
                        sp.SLCon = int.Parse(dr.GetValue(6).ToString());
                        sp.SLBan = int.Parse(dr.GetValue(7).ToString());
                        sp.GiaBan = int.Parse(dr.GetValue(8).ToString());
                        sp.GiaGoc = int.Parse(dr.GetValue(9).ToString());
                        sp.NgayCapNhat=DateTime.Parse( dr.GetValue(10).ToString());                      
                        sp.MaTT=int.Parse( dr.GetValue(11).ToString());
                        SanPhamCollection.Add(sp);
                    }
                dr.Close();
                totalRecords = int.Parse(cm.Parameters["@TotalRecords"].Value.ToString());
                return SanPhamCollection;
            
            }
    }
}
